package gob.osinerg.emergencia.dao;

import gob.osinerg.emergencia.bean.Dominio;
import gob.osinerg.emergencia.bean.Formulario;
import gob.osinerg.emergencia.bean.FormularioEmergDetalle;
import gob.osinerg.emergencia.bean.FormularioEmergenciaUnmay;
import gob.osinerg.emergencia.bean.MailBean;
import gob.osinerg.emergencia.util.Constants;
import gob.osinerg.emergencia.util.Converter;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.ResourceBundle;

import org.apache.commons.lang.StringUtils;
import org.apache.log4j.Logger;
import org.hibernate.Criteria;
import org.hibernate.Query;
import org.hibernate.criterion.Order;
import org.hibernate.criterion.Restrictions;
import org.springframework.orm.hibernate3.HibernateTemplate;

public class FormularioEmergenciaUnmayDAOimpl extends HibernateTemplate
		implements FormularioEmergenciaUnmayDAO {

	private Logger log = Logger
			.getLogger(FormularioEmergenciaUnmayDAOimpl.class);

	public List findEmergencia(FormularioEmergenciaUnmay bean) throws Exception {
		List lista = new ArrayList();
		ResultSet rs = null;
		PreparedStatement pstm = null;
		StringBuffer sql = new StringBuffer();
		sql
				.append("select sfeu.id, sfeu.nmro_emrgncia, crc2.rv_meaning as tpo_infrme, sfeu.flag_Temporal as flagTemporal, sfeu.nmro_expdnte as numeroSiged, ");
		sql
				.append("crc1.rv_meaning as tpo_emrgncia, to_char(sfeu.fcha_emrgncia,'dd/mm/yyyy') as fcha_emrgncia, sfu.id as idform ");
		sql
				.append("from sfh_frmlrio_emrgncia_unmay sfeu, sfh_frmlrio_unmay sfu, ");
		sql.append("cg_ref_codes crc1,  cg_ref_codes crc2 ");
		sql
				.append("where sfeu.frmlrio_id=sfu.id and crc1.rv_domain = 'EMERG_TIPO' ");
		sql
				.append("and crc2.rv_domain = 'EMERG_TIPO_FORM' and crc1.rv_low_value=sfeu.tpo_emrgncia ");
		sql.append("and crc2.rv_low_value=sfu.tpo_infrme ");
		System.out.println("nro_emrg: " + bean.getNemrg());
		System.out.println("tpo_inf: " + bean.getFormulario().getTpoInfrme());
		System.out.println("fcha_emrg: " + bean.getStrFecha());
		System.out.println("usuario: " + bean.getUsrioCrdor());
		if (bean.getNemrg().compareTo("") != 0) {
			sql.append("and sfeu.nmro_emrgncia like '%" + bean.getNemrg()
					+ "%' ");
		}
		if (bean.getFormulario().getTpoInfrme().compareTo("") != 0) {
			sql.append("and sfu.tpo_infrme='"
					+ bean.getFormulario().getTpoInfrme() + "' ");
		}
		if (bean.getStrFecha().compareTo("") != 0) {
			sql.append("and sfeu.fcha_emrgncia='" + bean.getStrFecha() + "' ");
		}
		if (bean.getUsrioCrdor().compareTo("") != 0) {
			sql.append("and sfeu.usrio_crdor='" + bean.getUsrioCrdor() + "' ");
		}
		sql.append("order by sfeu.nmro_emrgncia asc");
		System.out.println("SQL: " + sql.toString());

		try {
			pstm = getSession().connection().prepareStatement(sql.toString());
			rs = pstm.executeQuery();
			while (rs.next()) {
				FormularioEmergenciaUnmay feu = new FormularioEmergenciaUnmay();
				Formulario f = new Formulario();
				
				feu.setId(new Long(rs.getString("id")));
				feu.setNmroEmrgncia(rs.getString("nmro_emrgncia"));
				
				System.out.println(rs.getString("tpo_infrme")
						+ rs.getString("tpo_emrgncia")
						+ rs.getString("fcha_emrgncia"));
				
				f.setTpoInfrme(rs.getString("tpo_infrme"));
				feu.setFlagTemporal(rs.getString("flagTemporal"));
				feu.setNumeroExpediente(rs.getString("numeroSiged"));
				feu.setTpoEmrgncia(rs.getString("tpo_emrgncia"));
				feu.setStrFecha(rs.getString("fcha_emrgncia"));
				f.setId(new Long(rs.getString("idform")));
				
				feu.setFrmlarioId(new Long(rs.getString("idform")));
				feu.setFormulario(f);
				lista.add(feu);
			}
			pstm.close();
			pstm = null;
		} catch (Exception e) {
			log.error(e);
		} finally {
			if (pstm != null) {
				try {
					pstm.close();
				} catch (SQLException ignore) {
				}
				pstm = null;
			}
		}

		return lista;
	}

	public boolean getExisteFormularioFinal(FormularioEmergenciaUnmay bean)
			throws Exception {

		Criteria criteria = getSession().createCriteria(
				FormularioEmergenciaUnmay.class);
		criteria.createAlias("formulario", "f");
		criteria.add(Restrictions.eq("nmroEmrgncia", bean.getNmroEmrgncia()));
		criteria.add(Restrictions.eq("tpoEmrgncia", bean.getTpoEmrgncia()));
		criteria.add(Restrictions.eq("f.tpoInfrme",
				Constants.TIPO_INFORME_FINAL));

		return criteria.list().size() > 0 ? true : false;
	}

	public Object ResulsetToMap(ResultSet rs) throws Exception {
		Map map = new HashMap();
		ResultSetMetaData meta = rs.getMetaData();
		int numberOfColumns = meta.getColumnCount();
		for (int i = 1; i <= numberOfColumns; ++i) {
			String name = meta.getColumnName(i);
			Object value = rs.getObject(i);
			map.put(name, value);
		}
		return map;
	}

	public List getBusquedaTipoEmergencia(String tpo_informe) throws Exception {
		List lista = new ArrayList();
		ResultSet rs = null;
		PreparedStatement pstm = null;
		StringBuffer sql = new StringBuffer();
		sql.append("select tpo_emrgncia,nmbre " + "from sfh_frmlrio_unmay "
				+ "where tpo_infrme = '" + tpo_informe + "' " + "order by id ");
		try {
			pstm = getSession().connection().prepareStatement(sql.toString());
			rs = pstm.executeQuery();
			while (rs.next()) {
				Dominio bean = new Dominio();
				bean.setId(rs.getString("tpo_emrgncia"));
				bean.setDescripcion(rs.getString("nmbre"));
				lista.add(bean);

			}
			pstm.close();
			pstm = null;
		} catch (Exception e) {
			log.error(e);
		} finally {
			if (pstm != null) {
				try {
					pstm.close();
				} catch (SQLException ignore) {
				}
				pstm = null;
			}
		}

		return lista;
	}

	public int getFormato(String tabla, int ifform) throws Exception {
		int resultado = 0;
		ResultSet rs = null;
		PreparedStatement pstm = null;
		StringBuffer sql = new StringBuffer();
		sql.append("select id from " + tabla + " where frmlrio_emrgncia_id="
				+ ifform);
		try {
			pstm = getSession().connection().prepareStatement(sql.toString());
			rs = pstm.executeQuery();
			while (rs.next()) {
				resultado = rs.getInt("id");
			}
			pstm.close();
			pstm = null;
		} catch (Exception e) {
			log.error(e);
		} finally {
			if (pstm != null) {
				try {
					pstm.close();
				} catch (SQLException ignore) {
				}
				pstm = null;
			}
		}

		return resultado;
	}

	public ArrayList getReporte(String tabla, int ifform, int opc)
			throws Exception {
		ArrayList resultado = new ArrayList();
		ResultSet rs = null;
		PreparedStatement pstm = null;
		StringBuffer sql = new StringBuffer();
		System.out.println("isform" + ifform);
		switch (opc) {
		case 1:
			sql
					.append("select sfe.uniope_id, t.anio, t.mes, sfe.rgstro_dgh "
							+ "from "
							+ tabla
							+ " t, sfh_frmlrio_emrgncia_unmay sfe "
							+ "where t.frmlrio_emrgncia_id=sfe.id and sfe.id="
							+ ifform);
			try {
				pstm = getSession().connection().prepareStatement(
						sql.toString());
				rs = pstm.executeQuery();
				while (rs.next()) {
					resultado.add(0, rs.getString("uniope_id"));
					resultado.add(1, rs.getString("anio"));
					resultado.add(2, rs.getString("mes"));
					resultado.add(3, rs.getString("rgstro_dgh"));
				}
				pstm.close();
				pstm = null;
			} catch (Exception e) {
				log.error(e);
			} finally {
				if (pstm != null) {
					try {
						pstm.close();
					} catch (SQLException ignore) {
					}
					pstm = null;
				}
			}
			break;
		case 2:
			sql.append("select distinct sfe.rgstro_dgh "
					+ "from sfh_frmlrio_emrgncia_unmay sfe " + "where sfe.id="
					+ ifform);
			try {
				pstm = getSession().connection().prepareStatement(
						sql.toString());
				rs = pstm.executeQuery();
				while (rs.next()) {
					resultado.add(0, rs.getString("rgstro_dgh"));
				}
				pstm.close();
				pstm = null;
			} catch (Exception e) {
				log.error(e);
			} finally {
				if (pstm != null) {
					try {
						pstm.close();
					} catch (SQLException ignore) {
					}
					pstm = null;
				}
			}
			break;
		case 3:
			sql
					.append("select distinct sfe.uniope_id, t.anio, sfe.rgstro_dgh, t.smstre "
							+ "from "
							+ tabla
							+ " t, sfh_frmlrio_emrgncia_unmay sfe "
							+ "where t.frmlrio_emrgncia_id=sfe.id and sfe.id="
							+ ifform);
			try {
				pstm = getSession().connection().prepareStatement(
						sql.toString());
				rs = pstm.executeQuery();
				while (rs.next()) {
					resultado.add(0, rs.getString("uniope_id"));
					resultado.add(1, rs.getString("anio"));
					resultado.add(2, rs.getString("smstre"));
					resultado.add(3, rs.getString("rgstro_dgh"));
				}
				pstm.close();
				pstm = null;
			} catch (Exception e) {
				log.error(e);
			} finally {
				if (pstm != null) {
					try {
						pstm.close();
					} catch (SQLException ignore) {
					}
					pstm = null;
				}
			}
			break;
		case 4:
			sql
					.append("select distinct sfe.uniope_id, t.anio, sfe.rgstro_dgh, sed.smstre "
							+ "from sfh_estdstcas_ee t, sfh_estdstcas_ee_dtlle sed, sfh_frmlrio_emrgncia_unmay sfe "
							+ "where t.frmlrio_emrgncia_id=sfe.id and sed.estdstcas_enfrmdad_id=t.id and sfe.id="
							+ ifform);
			try {
				pstm = getSession().connection().prepareStatement(
						sql.toString());
				rs = pstm.executeQuery();
				while (rs.next()) {
					resultado.add(0, rs.getString("uniope_id"));
					resultado.add(1, rs.getString("anio"));
					resultado.add(2, rs.getString("smstre"));
					resultado.add(3, rs.getString("rgstro_dgh"));
				}
				pstm.close();
				pstm = null;
			} catch (Exception e) {
				log.error(e);
			} finally {
				if (pstm != null) {
					try {
						pstm.close();
					} catch (SQLException ignore) {
					}
					pstm = null;
				}
			}
			break;
		case 5:
			sql
					.append("select distinct sfe.uniope_id, sfe.rgstro_dgh "
							+ "from " + tabla
							+ " t, sfh_frmlrio_emrgncia_unmay sfe "
							+ "where t.frmlrio_emrgncia_id=sfe.id and sfe.id="
							+ ifform);
			try {
				pstm = getSession().connection().prepareStatement(
						sql.toString());
				rs = pstm.executeQuery();
				while (rs.next()) {
					resultado.add(0, rs.getString("uniope_id"));
					resultado.add(1, rs.getString("rgstro_dgh"));
				}
				pstm.close();
				pstm = null;
			} catch (Exception e) {
				log.error(e);
			} finally {
				if (pstm != null) {
					try {
						pstm.close();
					} catch (SQLException ignore) {
					}
					pstm = null;
				}
			}
			break;
		}

		return resultado;
	}
}
